﻿using NORM.Common;
using NORM.Configure;
using NORM.Entity;
using NORM.SQLObject;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;

namespace NORM.DataBase
{
    internal class PostgreSqlDataBase : DataBase, IDataBase
    {
        protected bool TransactionBegin = false;
        protected PostgreSqlHelper postgresql = new PostgreSqlHelper();

        private Npgsql.NpgsqlTransaction tran;
        private Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection();

        internal PostgreSqlDataBase()
        {
            this._sql = string.Empty;
            try
            {
                this._connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
                //解析连接字符串，解密
                if (string.IsNullOrEmpty(this._connectionString))
                    throw new Exception("连接字符串不能为空");
                SectionString[] SectionsString = Parse(this._connectionString);
                this._connectionString = Join(SectionsString);
                this._dataBaseType = DataBaseTypes.PostgreSqlDataBase;
                //初始化 conn
                this.conn = new NpgsqlConnection(_connectionString);
            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.ConfigException, ex.Message);
            }
        }

        internal PostgreSqlDataBase(string connectionString)
        {
            this._sql = string.Empty;
            this._connectionString = connectionString; //System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString;
            //解析连接字符串，解密
            SectionString[] SectionsString = Parse(this._connectionString);
            this._connectionString = Join(SectionsString);
            this._dataBaseType = DataBaseTypes.PostgreSqlDataBase;
            //初始化 conn
            this.conn = new NpgsqlConnection(_connectionString);
           
        }

        #region 私有方法

        int execute(OQL oql)
        {
            int val = -1;

            SetCommand(oql);

            if (TransactionBegin)
                val = postgresql.Execute(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                val = postgresql.Execute(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            return val;
        }

        bool insert<T>(T model) where T : EntityBase
        {
            bool rvl = false;
            if (model != null)
            {
                OQL oql = new OQL(model).Insert().End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }

            }
            return rvl;
        }

        bool update<T>(T model) where T : EntityBase
        {
            bool rvl = false;

            if (model != null)
            {
                OQL oql = new OQL(model).Update().End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }
            }            

            return rvl;
        }

        bool delete<T>(T model) where T : EntityBase
        {
            bool rvl = false;

            if (model != null)
            {
                List<Condition> conditions = new List<Condition>();

                Type _type = model.GetType();
                PropertyInfo[] properties = CacheProperties.cacheInstance.GetProperties(_type);  //_type.GetProperties();
                foreach (PropertyInfo pi in properties)
                {
                    if (pi.Name != "TableName" && pi.Name != "Properties")
                    {
                        object valud_d = pi.GetValue(model, null);
                        if (valud_d != null && DBNull.Value != valud_d)
                        {
                            Condition c = new Condition();
                            c.Field = pi.Name;
                            c.Value = valud_d;
                            conditions.Add(c);
                        }
                    }
                }

                OQL oql = new OQL(model).Delete().Where(conditions.ToArray()).End;

                if (execute(oql) > 0)
                {
                    rvl = true;
                }

            }

            return rvl;
        }

        #endregion

        #region 设置Command命令
        public void SetCommand(OQL oql)
        {
            Type _type = oql.Modelobject.GetType();
            #region Model 获取对象类型 Table View Function
            string classTypeName = "";
            object[] attrs = _type.GetCustomAttributes(false);// typeof(T)
            if (attrs != null && attrs.Length > 0)
            {
                foreach (object attr in attrs)
                {
                    if (attr.GetType().Equals(typeof(AtttribDescrib)))
                    {
                        classTypeName = attr.ToString();
                        break;
                    }
                }
            }
            #endregion  

            _sql = oql.ToString();
            Command =new OsqlCommand();
            if(classTypeName=="View")
                Command.CommandText = _sql.Replace('[', '"').Replace(']', '"');
            else
                Command.CommandText = _sql.Replace('[', '"').Replace(']', '"');

            List<NpgsqlParameter> _params = null;   

            if (oql.Parameters != null)
            {
                OsqlParameter[] osqlparams = oql.Parameters;
                switch (DataBaseType)
                {
                    case DataBaseTypes.PostgreSqlDataBase:
                        _params = new List<NpgsqlParameter>();
                        foreach (OsqlParameter p in osqlparams)
                        {
                            NpgsqlParameter p1 = new NpgsqlParameter("@" + p.Name, p.Value);
                            _params.Add(p1);
                        }
                        Command.Parameters = _params.ToArray();
                        break;
                }
            }

            base.validateOqlend(oql);
            //oql.Dispose();

        }
        #endregion

        #region 数据库对象

        public override DataTable GetDataBaseObject(string name, DataBaseSchema schema)
        {         
            DataTable dt = new DataTable();
            switch (schema)
            {
                case DataBaseSchema.Table:
                    dt = postgresql.GetTableColumns(this._connectionString, name);
                    break;
                case DataBaseSchema.View:
                    dt = postgresql.GetTableColumns(this._connectionString, name);
                    break;
            }
            return dt;
        }

        #endregion

        #region 事务操作

        public override void BeginTransaction()
        {
            conn = new NpgsqlConnection(this._connectionString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            tran = conn.BeginTransaction();
            postgresql.tran = tran;
            TransactionBegin = true;
        }

        public override int Commit()
        {
            try
            {
                tran.Commit();
                postgresql.tran = null;
            }
            catch (Exception ex)
            {

            }
            TransactionBegin = false;
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return 1;
        }

        public override void RollBack()
        {
            try
            {
                tran.Rollback();
                postgresql.tran = null;
            }
            catch
            {

            }
            TransactionBegin = false;
            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }

        #endregion

        #region  执行SQL脚本

        public override int Execute(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return postgresql.Execute(_connectionString, cmdType, cmdText, cmdParms);
        }

        public override DataSet GetDataSet(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return postgresql.GetDataSet(_connectionString, cmdType, cmdText, cmdParms);
        }

        #endregion

        #region 执行OQL

        public override int Execute(OQL oql)
        {            
            return this.execute(oql);
        }

        public override bool Insert<T>(T model)
        {
            return this.insert<T>(model);
        }

        public override bool Update<T>(T model)
        {
            return this.update<T>(model);
        }

        public override bool Delete<T>(T model)
        {
            return this.delete<T>(model);
        }

        public override bool Delete<T>(object PrimaryKeyValue)
        {
            T model = (T)Activator.CreateInstance(typeof(T));
            System.Reflection.PropertyInfo pi = typeof(T).GetProperty(model.PrimaryKey[0]);
            if (pi == null) throw new Exception("没有设置主键");            
          
            OQL oql = new OQL(model).Delete().Where(new Condition[] { new Condition() { Field = pi.Name, Value = PrimaryKeyValue } }).End;

            return this.execute(oql) > 0 ? true : false;
        }

        public override List<T> QueryList<T>(OQL oql, PageLimit page)
        {
            SetCommand(oql);  

            string _total_sql = "SELECT COUNT(1) FROM (" +
                Command.CommandText.TrimEnd(';') + " ) tn ";

            var count = postgresql.GetScalar(conn, CommandType.Text, _total_sql, Command.Parameters);

            int recordCount = 0;
            if (!Int32.TryParse(count + "", out recordCount))
            {
                return new List<T>();
            }

            page.RecordCount = recordCount;
            page.PageCount = (int)(decimal.Ceiling((decimal)(page.RecordCount * 1.0 / page.PageSize)));

            if (page.PageIndex <= 1) page.PageIndex = 1;
            if (page.PageIndex >= page.PageCount) page.PageIndex = page.PageCount;

            int startIndex = (page.PageIndex - 1) * page.PageSize;
            int endIndex = startIndex + page.PageSize;

            if (Command.CommandText.Contains("Limit") && oql.ChildOql == null)
                throw new NORMException(ExceptionType.DataBaseExceptoin, "分页查询中不能再含有Limit");

            Command.CommandText = Command.CommandText.TrimEnd(';') + " Limit " + page.PageSize + " Offset  " + startIndex + "";

            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = postgresql.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = postgresql.GetDataTable(_connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            var list = ConvertTo.ConvertToList<T>(dt);

            page.DataSourceView = dt.DefaultView;
            oql.RecordCount = page.RecordCount;

            return list;
        }

        public override List<T> QueryList<T>(OQL oql)
        {
            SetCommand(oql);

            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = postgresql.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = postgresql.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            var list = ConvertTo.ConvertToList<T>(dt);
            oql.RecordCount = list.Count;

            return list;
        }

        public override T QueryObject<T>(OQL oql)
        {
            SetCommand(oql);

            T t = null; //(T)Activator.CreateInstance(typeof(T));// new T();
            Command.CommandText = Command.CommandText.Replace("Limit 1", "").TrimEnd(';') + " Limit 1";
            DataTable dt = new DataTable();
            if (TransactionBegin)
                dt = postgresql.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
            else
                dt = postgresql.GetDataTable(_connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            List<T> list = ConvertTo.ConvertToList<T>(dt);
            foreach (T _t in list)
                t = _t;

            return t;           
        }

        public override T QueryModel<T>(object PrimaryKeyValue)
        {
            T t = (T)Activator.CreateInstance(typeof(T));// new T();

            if (t.PrimaryKey.Count <= 0)
                throw new Exception(t.TableName + "表还没有设置主键");

            OQL oql = new OQL(t).Select().Where(new Condition[] { 
                new Condition(){Field = t.PrimaryKey[0].ToString(), Comparison = "=", Value =PrimaryKeyValue } })
                .OrderBy(new string[] { t.PrimaryKey[0].ToString() }).End;

            SetCommand(oql); 
 
            t = QueryObject<T>(oql);

            return t;
        }

        public override DataTable QueryTable(OQL oql, PageLimit page = null)
        {
            DataTable dt = null;
            try
            {
                SetCommand(oql);

                if (page != null)
                {
                    string _total_sql = "SELECT COUNT(1) FROM (" +
                   Command.CommandText.TrimEnd(';') + " ) tn ";

                    var count = postgresql.GetScalar(conn, CommandType.Text, _total_sql, Command.Parameters);

                    int recordCount = 0;
                    if (!Int32.TryParse(count + "", out recordCount))
                    {
                        return new DataTable();
                    }

                    page.RecordCount = recordCount;
                    page.PageCount = (int)(decimal.Ceiling((decimal)(page.RecordCount * 1.0 / page.PageSize)));

                    if (page.PageIndex <= 1) page.PageIndex = 1;
                    if (page.PageIndex >= page.PageCount) page.PageIndex = page.PageCount;

                    int startIndex = (page.PageIndex - 1) * page.PageSize;
                    int endIndex = startIndex + page.PageSize;

                    Command.CommandText = Command.CommandText.TrimEnd(';') + " Limit " + page.PageSize + " Offset  " + startIndex + "";

                }

                if (TransactionBegin)
                    dt = postgresql.GetDataTable(conn, CommandType.Text, Command.CommandText, Command.Parameters);
                else
                    dt = postgresql.GetDataTable(this._connectionString, CommandType.Text, Command.CommandText, Command.Parameters);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion
    }
}
